﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Text;
using ReflectionStudio.Core.Events;
using INTEX.DataBase.Tools;
namespace WPFExcelReport
{

    public class FLTEKABData : BaseFunction
    {
        
        public FLTEKABData()
            : base()
        {

        }
        public FLTEKABData(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
        }

        public DataSet GetVKPOS(DataRow dwABPOS)
        {
            DataSet Result;
            DataSet Return = new DataSet();
            string TableName = "VKPOS";
            string ORDER = dwABPOS["ORDER"].ToString().Trim();
            string ARTICLE = dwABPOS["ARTICLE"].ToString().Trim();
            string SUPPLIER = dwABPOS["SUPPLIER"].ToString().Trim();
            string FKRS = dwABPOS["FKRS"].ToString().Trim();
            string FLFD = dwABPOS["FLFD"].ToString().Trim();
            string FROMDATE = dwABPOS["fromdate"].ToString().Trim();
            string ENDDATE = dwABPOS["enddate"].ToString().Trim();
            
            
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "select to_number(A.ABAVKAUFLFD) as CO, F.VKPNEUBEN,F.VKPART,F.VKPFARBE, "
                    + " F.VKPMENGE1*0.01 AS QTY,F.VKPLIEFTERM AS DELDATE, "
                    + " E.EKPAUFKRS||'-'||TO_NUMBER(E.EKPAUFLFD)||'-'||TO_NUMBER(E.EKPAUFPOS) AS PO, "
                    + " TRIM(G.LSNAME1) AS SUPPLIER, "
                    + " E.EKPME1*0.01 AS POQTY, E.EKPNEUD AS PODATE,E.EKPLTERM AS PFDATE,E.EKPME3*0.01 AS RECQTY,  "
                    + " (SELECT MAX(T.GPEINDAT) FROM GPDAT T "
                    + "  WHERE T.GPBESTKRS = E.EKPAUFKRS AND T.GPBESTLFD = E.EKPAUFLFD "
                    + "  AND T.GPBESTPOS = E.EKPAUFPOS ) AS RECDATE, "
                    + " a.abaaufkrs||'-'||TO_NUMBER(a.abaauflfd)||'-'||TO_NUMBER(a.abaaufpos) AS ORDNO, "
                    + " B.ABPSOLLMENGE5*0.01 AS ORDQTY,b.ABPZUGKG * 0.01 AS ALLQTY, "
                    + " B.ABPNEUDAT AS ORDDATE,B.ABPSTARTDAT AS OPSDATE,b.ABPLIEFDAT AS OPFDATE, "
                    + " b.ABPEAKG * 0.01 AS OFQTY,DECODE(b.ABPERLKZ, '', '', 'J', 'FINISHED') AS ORDSTATUS "
                    + " from abaufm a,ABPOS B,ABAUFM D,EKPOS E,VKPOS F,LIEFDAT G "
                    + " where A.ABAMANDANT = B.ABPMANDANT "
                    + " AND A.ABAAUFKRS = B.ABPAUFKRS AND A.ABAAUFLFD = B.ABPAUFLFD "
                    + " AND A.ABAAUFPOS = B.ABPAUFPOS AND A.ABAMANDANT = D.ABAMANDANT "
                    + " AND A.ABAAUFTYP = D.ABAVKAUFTYP AND A.ABAAUFKRS = D.ABAVKAUFKRS "
                    + " AND A.ABAAUFLFD = D.ABAVKAUFLFD AND A.ABAAUFPOS = D.ABAVKAUFPOS "
                    + " AND D.ABAMANDANT = E.EKPMANDANT AND D.ABAAUFKRS = E.EKPAUFKRS "
                    + " AND D.ABAAUFLFD = E.EKPAUFLFD AND D.ABAAUFPOS = E.EKPAUFPOS "
                    + " AND A.ABAMANDANT = F.VKPMANDANT AND A.ABAVKAUFKRS = F.VKPAUFKRS  "
                    + " AND A.ABAVKAUFLFD = F.VKPAUFLFD AND A.ABAVKAUFPOS = F.VKPAUFPOS "
                    + " AND E.EKPMANDANT = G.LSMANDANT AND E.EKPLIEFNR = G.LSLIEFNR "
                    + " AND a.abavkauftyp = 'K' AND E.EKPSA ='PO' AND F.VKPSA <>'AJ'  "
                    + " AND (TO_NUMBER(A.ABAVKAUFLFD) = '" + ORDER + "' OR '" + ORDER + "' = '999999') "
                    + " AND (TRIM(F.VKPART) LIKE '" + ARTICLE + "%' OR '" + ARTICLE + "' = 'AAA') "
                    + " AND (TO_NUMBER(E.EKPLIEFNR) = '" + SUPPLIER + "' OR '" + SUPPLIER + "'='99999999') "
                    + " AND (E.EKPAUFKRS = '" + FKRS + "' OR '" + FKRS + "'='99') "
                    + " AND (TO_NUMBER(E.EKPAUFLFD) = '" + FLFD + "' OR '" + FLFD + "'='999999') "
                    + " AND E.EKPNEUD >='" + FROMDATE + "' AND E.EKPNEUD <= '" + ENDDATE + "' "
                    + " ORDER BY PO,ORDNO "                   
                    ;
                
                Result = Connect2Proxy(orlReadCommand);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = TableName;
                    byte[] b;
                    string value;
                    foreach (DataRow dw in Result.Tables[TableName].Rows)
                    {
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["SUPPLIER"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["SUPPLIER"] = value;
                         
                    }
                }                
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return Return;
        }
       
        public DataSet BuildFLTEKABData(DataRow dwUI)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                setResult = GetVKPOS(dwUI);
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        
    }
}